package com.quanquan.user.service.autoCompute;

import com.quanquan.user.service.autoCompute.constant.SourceTableConst;
import com.quanquan.user.service.autoCompute.constant.SymbolConst;
import com.quanquan.user.service.autoCompute.constant.TypeConst;
import com.quanquan.user.service.autoCompute.entity.MemberLabel;
import com.quanquan.user.service.autoCompute.enums.OptionType;
import lombok.extern.slf4j.Slf4j;
import utils.StringUtil;

import java.util.List;
import java.util.Map;

/**
 * @ClassName JavaSqlUtil
 * @Description
 * @Author Owen
 * @Date 2020/2/27 0027 下午 3:06
 * @Version V1.0
 **/
@Slf4j
public class JavaSqlUtil {

    public static String assembleCountSql(Map<String , List<MemberLabel>> map){
        StringBuffer sql = new StringBuffer("SELECT COUNT(DISTINCT u.id) AS count FROM oo_user u");
        StringBuffer end = new StringBuffer();
        map.forEach((k,v)->{
            switch (k){
                case SourceTableConst.CONSUME:
                    sql.append(SymbolConst.SPACE).append(SymbolConst.INNER_JOIN);
                    sql.append(assembleConsumeSql(v,"c","cc"));
                    break;
                case SourceTableConst.INTEGRAL:
                    sql.append(SymbolConst.SPACE).append(SymbolConst.INNER_JOIN);
                    sql.append(assembleIntegralSql(v,"i","ii"));
                    break;
                case SourceTableConst.PARK:
                    sql.append(SymbolConst.SPACE).append(SymbolConst.INNER_JOIN);
                    sql.append(assembleParkSql(v,"p","pp"));
                    break;
                case SourceTableConst.COUPON:
                    break;
                default:
                    end.append(SymbolConst.SPACE).append(SymbolConst.WHERE);
                    end.append(assembleMemberSql(v,"u"));
                    break;
            }
        });
        return sql.append(end).toString();
    }

    public static String assembleSelectSql(Map<String , List<MemberLabel>> map){

        StringBuffer sql = new StringBuffer("SELECT u.id,u.phone,u.name,u.member_card_num,u.gender,u.age,u.birthday,u.create_time FROM oo_user u");
        StringBuffer end = new StringBuffer();
        map.forEach((k,v)->{
            switch (k){
                case SourceTableConst.CONSUME:
                    sql.append(SymbolConst.SPACE).append(SymbolConst.INNER_JOIN);
                    sql.append(assembleConsumeSql(v,"c","cc"));
                    break;
                case SourceTableConst.INTEGRAL:
                    sql.append(SymbolConst.SPACE).append(SymbolConst.INNER_JOIN);
                    sql.append(assembleIntegralSql(v,"i","ii"));
                    break;
                case SourceTableConst.PARK:
                    sql.append(SymbolConst.SPACE).append(SymbolConst.INNER_JOIN);
                    sql.append(assembleParkSql(v,"p","pp"));
                    break;
                case SourceTableConst.COUPON:
                    break;
                default:
                    end.append(SymbolConst.SPACE).append(SymbolConst.WHERE);
                    end.append(assembleMemberSql(v,"u"));
                    break;
            }
        });
        return sql.append(end).toString();
    }

    public static String assembleMemberLabelCountSql(MemberLabel label){
        String table = label.getLabelFrom();
        OptionType optionType = label.getOptionType();
        StringBuffer sql = new StringBuffer();

        switch (table){
            case SourceTableConst.MEMBER:
                break;
            case SourceTableConst.CONSUME:
                break;
            case SourceTableConst.INTEGRAL:
                break;
            case SourceTableConst.PARK:
                break;
            case SourceTableConst.COUPON:
                break;
                default:
        }
        return sql.toString();
    }

    public static String assembleMemberSql(List<MemberLabel> list,String alias){

        StringBuffer sql = new StringBuffer();
        StringBuffer suffix = new StringBuffer();
        for (MemberLabel s : list) {

            switch (s.getOptionType()) {
                case SPAN:
                    suffix.append(assembleSpan(s, alias));
                    break;
                case POINT:
                case BOOL:
                    suffix.append(assemblePoint(s, alias));
                    break;
                case RANGE:
                    suffix.append(assembleRange(s, alias));
                    break;
                case FREQ:
                    break;
                case FUZZY:
                    break;
                default:

            }

            if (list.indexOf(s) + 1 != list.size()) {
                suffix.append(SymbolConst.SPACE).append(SymbolConst.AND);
            }

        }
        sql.append(suffix);
        return sql.toString();
    }

    public static String assembleIntegralSql(List<MemberLabel> list,String alias,String temAlias){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.SPACE).append(SymbolConst.LBRACKET);
        //select * ,sum(integral_point) as sum from oo_integral_detail where

        sql.append(" select user_id,sum(integral_point) as sum from oo_integral_trading_details").append(SymbolConst.SPACE).append(alias);

        StringBuffer suffix = new StringBuffer();
        StringBuffer end = new StringBuffer();
        end.append(SymbolConst.SPACE).append(SymbolConst.GROUP_BY).append(SymbolConst.SPACE).append("user_id");

        boolean first = true;

        for (MemberLabel s : list) {
            if(null != s.getStatisticType() && StringUtil.isNotEmpty(s.getStatisticType().toString())){
                switch (s.getStatisticType()){
                    case SUM:
                        end.append(assembleSum(s));
                        break;
                    case COUNT:
                        end.append(assembleCount(s));
                        break;
                    default:
                }
            }else{
                if (!first) {
                    suffix.append(SymbolConst.SPACE).append(SymbolConst.AND);
                }
                if (first) {
                    first = false;
                    suffix.append(SymbolConst.SPACE).append(SymbolConst.WHERE);
                    switch (s.getCentType()){
                        case GET:
                            suffix.append(SymbolConst.SPACE).append("trade_action = 0 and ");
                            break;
                        case COST:
                            suffix.append(SymbolConst.SPACE).append("trade_action = 1 and ");
                            break;
                            default:
                    }
                }

                switch (s.getOptionType()) {
                    case SPAN:
                        suffix.append(assembleSpan(s, alias));
                        break;
                    case POINT:
                    case BOOL:
                        suffix.append(assemblePoint(s, alias));
                        break;
                    case RANGE:
                        suffix.append(assembleRange(s, alias));
                        break;
                    case FREQ:
                        break;
                    case FUZZY:
                        break;
                    default:

                }
            }
        }
        sql.append(suffix).append(end);
        sql.append(SymbolConst.RBRACKET).append(SymbolConst.SPACE).append(temAlias).append(SymbolConst.SPACE)
        .append(SymbolConst.ON).append(SymbolConst.SPACE).append("u.id").append(SymbolConst.SPACE).append(SymbolConst.EQ)
        .append(SymbolConst.SPACE).append(temAlias).append(".user_id");
        return sql.toString();
    }

    public static String assembleParkSql(List<MemberLabel> list,String alias,String temAlias){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.SPACE).append(SymbolConst.LBRACKET);
        //select * ,sum(integral_point) as sum from oo_integral_detail where

        sql.append(" select user_id,count(1) as count from oo_parking_record").append(SymbolConst.SPACE).append(alias);

        StringBuffer suffix = new StringBuffer();
        StringBuffer end = new StringBuffer();
        end.append(SymbolConst.SPACE).append(SymbolConst.GROUP_BY).append(SymbolConst.SPACE).append("user_id");

        boolean first = true;

        for (MemberLabel s : list) {
            if(null != s.getStatisticType() && StringUtil.isNotEmpty(s.getStatisticType().toString())){
                switch (s.getStatisticType()){
                    case SUM:
                        end.append(assembleSum(s));
                        break;
                    case COUNT:
                        end.append(assembleCount(s));
                        break;
                    default:
                }
            }else{
                if (!first) {
                    suffix.append(SymbolConst.SPACE).append(SymbolConst.AND);
                }
                if (first) {
                    first = false;
                    suffix.append(SymbolConst.SPACE).append(SymbolConst.WHERE).append(SymbolConst.SPACE)
                    .append("status = 1 and");
                }

                switch (s.getOptionType()) {
                    case SPAN:
                        suffix.append(assembleSpan(s, alias));
                        break;
                    case POINT:
                    case BOOL:
                        suffix.append(assemblePoint(s, alias));
                        break;
                    case RANGE:
                        suffix.append(assembleRange(s, alias));
                        break;
                    case FREQ:
                        break;
                    case FUZZY:
                        break;
                    default:

                }
            }
        }
        sql.append(suffix).append(end);
        sql.append(SymbolConst.RBRACKET).append(SymbolConst.SPACE).append(temAlias).append(SymbolConst.SPACE)
                .append(SymbolConst.ON).append(SymbolConst.SPACE).append("u.id").append(SymbolConst.SPACE).append(SymbolConst.EQ)
                .append(SymbolConst.SPACE).append(temAlias).append(".user_id");
        return sql.toString();
    }

    public static String assembleConsumeSql(List<MemberLabel> list,String alias,String temAlias){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.SPACE).append(SymbolConst.LBRACKET);

        sql.append(" select user_id,count(1) as count from oo_consumption_amount").append(SymbolConst.SPACE).append(alias);

        StringBuffer suffix = new StringBuffer();
        StringBuffer end = new StringBuffer();
        end.append(SymbolConst.SPACE).append(SymbolConst.GROUP_BY).append(SymbolConst.SPACE).append("user_id");

        boolean first = true;

        for (MemberLabel s : list) {
            if(null != s.getStatisticType() && StringUtil.isNotEmpty(s.getStatisticType().toString())){
                switch (s.getStatisticType()){
                    case SUM:
                        end.append(assembleSum(s));
                        break;
                    case COUNT:
                        end.append(assembleCount(s));
                        break;
                    default:
                }
            }else{
                if (!first) {
                    suffix.append(SymbolConst.SPACE).append(SymbolConst.AND);
                }
                if (first) {
                    first = false;
                    suffix.append(SymbolConst.SPACE).append(SymbolConst.WHERE).append(SymbolConst.SPACE)
                            .append("1 = 1 and");
                }

                switch (s.getOptionType()) {
                    case SPAN:
                        suffix.append(assembleSpan(s, alias));
                        break;
                    case POINT:
                    case BOOL:
                        suffix.append(assemblePoint(s, alias));
                        break;
                    case RANGE:
                        suffix.append(assembleRange(s, alias));
                        break;
                    case FREQ:
                        break;
                    case FUZZY:
                        break;
                    default:

                }
            }
        }
        sql.append(suffix).append(end);
        sql.append(SymbolConst.RBRACKET).append(SymbolConst.SPACE).append(temAlias).append(SymbolConst.SPACE)
                .append(SymbolConst.ON).append(SymbolConst.SPACE).append("u.id").append(SymbolConst.SPACE).append(SymbolConst.EQ)
                .append(SymbolConst.SPACE).append(temAlias).append(".user_id");
        return sql.toString();
    }

    public static String assemblePoint(MemberLabel memberLabel,String alias){
        StringBuffer sql = new StringBuffer();
        return sql.append(SymbolConst.SPACE).append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).append(SymbolConst.SPACE)
                .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue())
                .toString();
    }

    public static String assembleSpan(MemberLabel memberLabel,String alias){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.SPACE).append(memberLabel.getOption())
                .append(SymbolConst.LBRACKET)
                .append(alias).append(SymbolConst.POINT)
                .append(memberLabel.getFieldName())
                .append(SymbolConst.RBRACKET)
                .append(SymbolConst.SPACE)
                .append(SymbolConst.EQ)
                .append(SymbolConst.SPACE)
                .append(memberLabel.getOption())
                .append(SymbolConst.LBRACKET)
                .append(SymbolConst.CURDATE)
                .append(SymbolConst.LBRACKET)
                .append(SymbolConst.RBRACKET)
                .append(SymbolConst.RBRACKET);

        return sql.toString();
    }

    public static String assembleRange(MemberLabel memberLabel,String alias){
        StringBuffer sql = new StringBuffer();

        if(StringUtil.isNotEmpty(memberLabel.getLeftValue()) && StringUtil.isEmpty(memberLabel.getRightValue())){
            if(memberLabel.getFieldType().equals(TypeConst.DATE)){
                sql.append(SymbolConst.SPACE).append(strToSqlDate(new StringBuffer().append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).toString())).append(SymbolConst.SPACE)
                        .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(strToSqlDate("'" + memberLabel.getLeftValue() + "'"));
            }else{
                sql.append(SymbolConst.SPACE).append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).append(SymbolConst.SPACE)
                        .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(memberLabel.getLeftValue());
            }
        }else if(StringUtil.isEmpty(memberLabel.getLeftValue()) && StringUtil.isNotEmpty(memberLabel.getRightValue())){
            if(memberLabel.getFieldType().equals(TypeConst.DATE)){
                sql.append(SymbolConst.SPACE).append(strToSqlDate(new StringBuffer().append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).toString())).append(SymbolConst.SPACE)
                        .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(strToSqlDate("'" + memberLabel.getRightValue() + "'"));
            }else  {
                sql.append(SymbolConst.SPACE).append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).append(SymbolConst.SPACE)
                        .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue());
            }
        }else{
            if(memberLabel.getFieldType().equals(TypeConst.DATE)){
                sql.append(SymbolConst.SPACE).append(strToSqlDate(new StringBuffer().append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).toString())).append(SymbolConst.SPACE)
                        .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(strToSqlDate("'" + memberLabel.getLeftValue() + "'"))
                        .append(SymbolConst.SPACE).append(SymbolConst.AND).append(SymbolConst.SPACE)
                        .append(strToSqlDate(new StringBuffer().append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).toString())).append(SymbolConst.SPACE)
                        .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(strToSqlDate("'" + memberLabel.getRightValue() + "'"));
            }else{
                sql.append(SymbolConst.SPACE).append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).append(SymbolConst.SPACE)
                        .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(memberLabel.getLeftValue())
                        .append(SymbolConst.SPACE).append(SymbolConst.AND).append(SymbolConst.SPACE)
                        .append(alias).append(SymbolConst.POINT).append(memberLabel.getFieldName()).append(SymbolConst.SPACE)
                        .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue());
            }

        }

        return sql.toString();
    }

    public static String assembleSum(MemberLabel memberLabel){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.SPACE).append(SymbolConst.HAVING);

        if(StringUtil.isNotEmpty(memberLabel.getLeftValue()) && StringUtil.isEmpty(memberLabel.getRightValue())){
            sql.append(SymbolConst.SPACE).append("sum").append(SymbolConst.SPACE)
                    .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(memberLabel.getLeftValue());
        }else if(StringUtil.isEmpty(memberLabel.getLeftValue()) && StringUtil.isNotEmpty(memberLabel.getRightValue())){
            sql.append(SymbolConst.SPACE).append("sum").append(SymbolConst.SPACE)
                    .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue());
        }else{
            sql.append(SymbolConst.SPACE).append("sum").append(SymbolConst.SPACE)
                    .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(memberLabel.getLeftValue())
                    .append(SymbolConst.SPACE).append(SymbolConst.AND).append(SymbolConst.SPACE)
                    .append("sum").append(SymbolConst.SPACE)
                    .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue());
        }

        return sql.toString();
    }

    public static String assembleCount(MemberLabel memberLabel){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.SPACE).append(SymbolConst.HAVING);

        if(StringUtil.isNotEmpty(memberLabel.getLeftValue()) && StringUtil.isEmpty(memberLabel.getRightValue())){
            sql.append(SymbolConst.SPACE).append("count").append(SymbolConst.SPACE)
                    .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(memberLabel.getLeftValue());
        }else if(StringUtil.isEmpty(memberLabel.getLeftValue()) && StringUtil.isNotEmpty(memberLabel.getRightValue())){
            sql.append(SymbolConst.SPACE).append("count").append(SymbolConst.SPACE)
                    .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue());
        }else{
            sql.append(SymbolConst.SPACE).append("count").append(SymbolConst.SPACE)
                    .append(memberLabel.getLeftSymbol()).append(SymbolConst.SPACE).append(memberLabel.getLeftValue())
                    .append(SymbolConst.SPACE).append(SymbolConst.AND).append(SymbolConst.SPACE)
                    .append("count").append(SymbolConst.SPACE)
                    .append(memberLabel.getRightSymbol()).append(SymbolConst.SPACE).append(memberLabel.getRightValue());
        }

        return sql.toString();
    }

    public static String strToSqlDate(String strDate){
        StringBuffer str = new StringBuffer();
        str.append("str_to_date(").append(strDate.trim()).append(",'%Y-%m-%d %H:%i:%s')");
        return str.toString();
    }

    /**
     * 拼接单一属性
     * @param field 字段
     * @param symbol 符号
     * @param value 值
     * @return
     */
    public static String assembleSingle(String field,String symbol,String value){
        StringBuffer sql = new StringBuffer();
        if (SymbolConst.LIKE.equals(symbol)) //拼接like语句
            sql.append(field)
                    .append(SymbolConst.SPACE)
                    .append(symbol)
                    .append(SymbolConst.SPACE)
                    .append(SymbolConst.QUA)
                    .append(SymbolConst.PER)
                    .append(value)
                    .append(SymbolConst.PER)
                    .append(SymbolConst.QUA);
        else //非like语句
            sql.append(field)
                    .append(SymbolConst.SPACE)
                    .append(symbol)
                    .append(SymbolConst.SPACE)
                    .append(SymbolConst.QUA)
                    .append(value)
                    .append(SymbolConst.QUA);
        return sql.toString();
    }

    /**
     *
     * @param field
     * @param symbol
     * @param value
     * @return
     */
    public static String assembleSingleStrNum(String field,String symbol,String value){
        StringBuffer sql = new StringBuffer();
        if (SymbolConst.LIKE.equals(symbol)) //拼接like语句
            sql.append(field)
                    .append(SymbolConst.SPACE)
                    .append(symbol)
                    .append(SymbolConst.SPACE)
                    .append(SymbolConst.QUA)
                    .append(SymbolConst.PER)
                    .append(value)
                    .append(SymbolConst.PER)
                    .append(SymbolConst.QUA);
        else //非like语句
            sql.append(field)
                    .append(SymbolConst.SPACE)
                    .append(symbol)
                    .append(SymbolConst.SPACE)
                    .append(SymbolConst.QUA)
                    .append(value)
                    .append(SymbolConst.QUA);
        return sql.toString();
    }

    /**
     * 日期双属性拼接
     * @param field 字段
     * @param operator 运算符：AND,OR
     * @param leftValue 左边值
     * @param leftSymbol 左边符号
     * @param rightValue 右边值
     * @param rightSymbol 右边符号
     * @return
     */
    public static String assembleDate(String field,String operator,String leftValue,String leftSymbol,String rightValue,String rightSymbol){
        StringBuffer sql = new StringBuffer();
        if(StringUtil.isNotEmpty(leftValue) && StringUtil.isNotEmpty(rightValue)){
            sql.append(SymbolConst.LBRACKET)
                    .append(strToSqlDate(field))
                    .append(SymbolConst.SPACE)
                    .append(leftSymbol)
                    .append(SymbolConst.SPACE)
                    .append(strToSqlDate("'" + leftValue + "'"))
                    .append(SymbolConst.SPACE)
                    .append(operator)
                    .append(SymbolConst.SPACE)
                    .append(strToSqlDate(field))
                    .append(SymbolConst.SPACE)
                    .append(rightSymbol)
                    .append(SymbolConst.SPACE)
                    .append(strToSqlDate("'" + rightValue + "'"))
                    .append(SymbolConst.RBRACKET);
        }else{
            sql.append(strToSqlDate(field))
                    .append(SymbolConst.SPACE)
                    .append(leftSymbol)
                    .append(SymbolConst.SPACE)
                    .append(strToSqlDate("'" + leftValue + "'"))
                    .append(SymbolConst.SPACE);
        }
        return sql.toString();
    }

    /**
     * 字符串或数字类型双属性拼接
     * @param field 字段
     * @param operator 运算符
     * @param leftValue 左边值
     * @param leftSymbol 左边符号
     * @param rightSymbol 右边值
     * @param rightValue 右边符号
     * @return
     */
    public static String assembleStrNum(String field,String operator,String leftValue, String leftSymbol, String rightValue,String rightSymbol){
        StringBuffer sql = new StringBuffer();
        sql.append(SymbolConst.LBRACKET)
                .append(field)
                .append(SymbolConst.SPACE)
                .append(leftSymbol)
                .append(SymbolConst.SPACE)
                .append(leftValue)
                .append(SymbolConst.SPACE)
                .append(operator)
                .append(SymbolConst.SPACE)
                .append(field)
                .append(SymbolConst.SPACE)
                .append(rightSymbol)
                .append(SymbolConst.SPACE)
                .append(rightValue)
                .append(SymbolConst.RBRACKET);
        return sql.toString();
    }

    /**
     * 拼接  年、季、月、周
     * @param field 字段
     * @param symbol 年、季、月、周
     * @return
     */
    public static String assembleDateRange(String field, String symbol){
        StringBuffer sql = new StringBuffer()
                .append(symbol)
                .append(SymbolConst.LBRACKET)
                .append(field)
                .append(SymbolConst.RBRACKET)
                .append(SymbolConst.SPACE)
                .append(SymbolConst.EQ)
                .append(SymbolConst.SPACE)
                .append(symbol)
                .append(SymbolConst.LBRACKET)
                .append(SymbolConst.CURDATE)
                .append(SymbolConst.LBRACKET)
                .append(SymbolConst.RBRACKET)
                .append(SymbolConst.RBRACKET);
        return sql.toString();
    }
}
